package com.apes.fa.respository;

import com.apes.fa.model.FaCardAccount;
import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

public interface FaCardAccountRepository extends CustomRepository<FaCardAccount, String> {


    @Query(value = "" +
            " SELECT " +
            " * " +
            " FROM " +
            " fa_card_account f" +
            " WHERE " +
            " f.fa_card_id = :id" +
            " ORDER BY f.BOOKKEEPING_DATE ASC "
            , nativeQuery = true)
    List<FaCardAccount> findAllByFaCardId(@Param("id") String id);

    @Query(value = "select * " +
            "  from fa_card_account a " +
            " where a.summary in (4, 5) " +
            "   and a.increase_depreciation != 0 " +
            "   and a.company_id = :company_id " +
            "   and a.bookkeeping_date >= TO_DATE(:startDate,'yyyy-mm-dd hh24:mi:ss') " +
            "   and a.bookkeeping_date <= TO_DATE(:endDate,'yyyy-mm-dd hh24:mi:ss') "
            , nativeQuery = true)
    List<FaCardAccount> findAll(@Param("company_id") String companyId, @Param("startDate") String startDate, @Param("endDate") String endDate);

    @Query(value = "SELECT SUM(A.INCREASE_DEPRECIATION) ZJJE,B.DEPT_ID deptId" +
            "  FROM FA_CARD_ACCOUNT A, FA_CARD B " +
            " WHERE A.FA_CARD_ID = B.ID " +
            "   AND EXTRACT(MONTH FROM A.BOOKKEEPING_DATE) = :months " +
            "   AND EXTRACT(YEAR FROM A.BOOKKEEPING_DATE) = :years " +
            "   AND A.INCREASE_DEPRECIATION IS NOT NULL " +
            "   GROUP BY B.DEPT_ID",nativeQuery = true)
    List<Map<String,Object>> findAllByYearMonth(String months, String years);
}
